package com.css.fxfzdzzh.modules.basicData.repository.impl;

import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.modules.basicData.entity.LandForm;
import com.css.fxfzdzzh.modules.basicData.entity.SensingImage;
import com.css.fxfzdzzh.modules.basicData.landslideSite.entity.LandslideSiteFileData;
import com.css.fxfzdzzh.modules.basicData.vo.QueryByVersionParam;
import com.css.fxfzdzzh.modules.eqLandslide.lawAnalysis.vo.SlopesVO;
import com.css.fxfzdzzh.modules.tifData.service.ElevationMapService;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年02月28日  17:11
 */
@Repository
public class SensingImageRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    ElevationMapService elevationMapService;

    public void updateByVersion(String tempVersionCode, String versionCode, Integer dataRangeCountry) {
        String sql = "update dzzh_sys_sensing_image  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }
    public void deleteByVersion(String versionCode) {
    String sql ="update dzzh_sys_sensing_image  set del_flag = '1' where data_version='"+ versionCode+ "' and del_flag='"+YNEnum.N+"'";
        jdbcTemplate.update(sql);
    }

    public void updateLs(String versionCode, String tempVersionCode) {
        String sql = "update dzzh_sys_sensing_image  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public void byShpBatchSave(List<SensingImage> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql =
                "insert into dzzh_sys_sensing_image(dssi_id,"
                        + "data_version,"
                        + "name,"
                        + "outdoor_num,"
                        + "landslide_time,"
                        + "landslide_year,"
                        + "landslide_month,"
                        + "landslide_day,"
                        + "landslide_type,"
                        + "landslide_shape,"
                        + "crown,"
                        + "toe,"
                        + "longitude,"
                        + "latitude,"
                        + "town,"
                        + "village,"
                        + "under_age,"
                        + "formation_lithology,"
                        + "structural_position,"
                        + "earthquake_intensity,"
                        + "stratigraphic_dip,"
                        + "stratigraphic_angle,"
                        + "microrelief,"
                        + "relative_river_position,"
                        + "slope_structure_type,"
                        + "control_structure_type,"
                        + "control_structure_inclination,"
                        + "control_structure_angle,"
                        + "landslide_length,"
                        + "landslide_width,"
                        + "landslide_thickness,"
                        + "landslide_slope,"
                        + "landslide_aspect,"
                        + "landslide_area,"
                        + "landslide_volume,"
                        + "landslide_plane_shape,"
                        + "landslide_profile_shape,"
                        + "scale_grade,"
                        + "create_time,"
                        + "create_user,"
                        + "create_user_name,"
                        + "province,"
                        + "city,"
                        + "country,"
                        + "del_flag,geom) "
                        + " values("
                        + ":dssiId, "
                        + ":versionCode, "
                        + ":name, "
                        + ":outdoorNum, "
                        + ":landslideTime, "
                        + ":landslideYear, "
                        + ":landslideMonth, "
                        + ":landslideDay,"
                        + ":landslideType,"
                        + ":landslideShape,"
                        + ":crown,"
                        + ":toe,"
                        + ":longitude,"
                        + ":latitude,"
                        + ":town, "
                        + ":village, "
                        + ":underAge,"
                        + ":formationLithology,"
                        + ":structuralPosition,"
                        + ":earthquakeIntensity,"
                        + ":stratigraphicDip,"
                        + ":stratigraphicAngle,"
                        + ":microrelief,"
                        + ":relativeRiverPosition,"
                        + ":slopeStructureType,"
                        + ":controlStructureType,"
                        + ":controlStructureInclination,"
                        + ":controlStructureAngle,"
                        + ":landslideLength,"
                        + ":landslideWidth,"
                        + ":landslideThickness,"
                        + ":landslideSlope,"
                        + ":landslideAspect,"
                        + ":landslideArea,"
                        + ":landslideVolume,"
                        + ":landslidePlaneShape,"
                        + ":landslideProfileShape,"
                        + ":scaleGrade,"
                        + ":createTime,"
                        + ":createUser,"
                        + ":createUserName,"
                        + ":province,"
                        + ":city,"
                        + ":country,"
                        + ":delFlag,ST_GeometryFromText(:geom,4490)"
                        + " )";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public List<SlopesVO> getSlopeAreaByGeomAndVersion1(String geomText, String geomText1, Integer number1, Integer number2, String param2) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText(geomText);
        String sql = "select x.longitude longitude,x.latitude latitude,x.landslide_area landslideArea,x.dssi_id id from dzzh_sys_sensing_image x " +
                "where st_intersects(st_geometryfromtext('POINT(' || x.longitude || ' ' || x.latitude || ')',4490)" +
                ",st_geomfromtext((select st_astext(st_difference(st_intersection( ";
        if (number2 == 300000||number2==2000) {
            sql = sql + "(select st_geometryfromtext(?,4490) as polygon)),";
        } else {
            sql = sql + "st_transform(st_buffer(st_transform(a.geom, "+epsg+"),?),4490),";
        }
        sql = sql + "(select st_geometryfromtext(?,4490) as polygon)), st_intersection(st_transform(st_buffer(st_transform(a.geom, "+epsg+"),?),4490), " +
                "(select st_geometryfromtext(?,4490) as polygon)))) as geom from (select st_intersection((select st_geometryfromtext(?,4490) as line), " +
                "(select st_geometryfromtext(?,4490) as polygon)) as geom) a),4490)) and data_version=? ";
        if (number2 == 300000||number2==2000) {
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SlopesVO.class)
                    , geomText, geomText, number2, geomText1, geomText, geomText, param2);
        } else {
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SlopesVO.class)
                    , number1, geomText, number2, geomText1, geomText, geomText, param2);
        }
    }

    public List<SlopesVO> getSlopeAreaByGeomAndVersion1(String geomText, String param3) {
        String sql = "select x.longitude longitude,x.latitude latitude,x.landslide_area landslideArea,x.dssi_id id from dzzh_sys_sensing_image x " +
                "where st_intersects(st_geometryfromtext('POINT(' || x.longitude || ' ' || x.latitude || ')',4490)" +
                ",st_geomfromtext(?,4490)) and data_version=? ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SlopesVO.class), geomText, param3);
    }

    public List<Map<String, Object>> getByGeomAndVersion2(String tableName, String param2) {
        String sql = "select b.gridvalue gridvalue,b.smarea smarea,sum(a.landslide_area) sum,st_AsText(b.smgeometry) smgeometry" +
                " from dzzh_sys_sensing_image a,"+tableName+" b" +
                " where a.data_version='"+param2+"' and st_intersects(a.geom, b.smgeometry) GROUP BY b" +
                ".gridvalue, b.smarea,b.smgeometry";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }

    public List<SensingImage> getYwData(QueryByVersionParam queryByVersionParam) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_sensing_image where 1=1 and data_version='"+queryByVersionParam.getVersionCode()+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        queryByDataVersionSQLBuild(queryByVersionParam, stringBuilder);
        stringBuilder.append(" order by create_time desc ");
        if (queryByVersionParam.getPage() != null && queryByVersionParam.getRows() != null) {
            stringBuilder.append(" limit ").append(queryByVersionParam.getRows()).append(" offset ").append(queryByVersionParam.getRows() * (Math.max(queryByVersionParam.getPage(), 1) - 1));
        }
        return jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SensingImage.class));
    }

    private void queryByDataVersionSQLBuild(QueryByVersionParam queryByVersionParam,StringBuilder stringBuilder) {
        if (org.apache.commons.lang3.StringUtils.isNotBlank(queryByVersionParam.getProvince())) {
            stringBuilder.append(" and province = '").append(queryByVersionParam.getProvince()).append("' ");
        }
        if (org.apache.commons.lang3.StringUtils.isNotBlank(queryByVersionParam.getCity())) {
            stringBuilder.append(" and city = '").append(queryByVersionParam.getCity()).append("' ");
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCounty())) {
            stringBuilder.append(" and county = '").append(queryByVersionParam.getCounty()).append("' ");
        }
    }

    public Long countByDataVersion(QueryByVersionParam queryByVersionParam) {
        StringBuilder sql = new StringBuilder("select count(*) from dzzh_sys_sensing_image where del_flag = '0' and data_version = '" + queryByVersionParam.getVersionCode() + "'");
        queryByDataVersionSQLBuild(queryByVersionParam, sql);
        return jdbcTemplate.queryForObject(sql.toString(), Long.class);
    }

    public Integer getValidDataNum(String versionCode) {
        String sql = "select count(*) from dzzh_sys_sensing_image where del_flag='0' and data_version='"+versionCode+"'";
        Integer rowNum = jdbcTemplate.queryForObject(sql, Integer.class);
        if(rowNum==null){
            return 0;
        }
        return rowNum;
    }

    public SensingImage getYwDetails(String id) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_sensing_image where 1=1 and dssi_id='"+id+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        List<SensingImage> query = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SensingImage.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new SensingImage();
    }

    public List<SensingImage> getDataForDp(){
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_sensing_image where 1=1  and del_flag='"+ YNEnum.N.toString()+"' ");
        List<SensingImage> query = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(SensingImage.class));
        return PlatformObjectUtils.isNotEmpty(query)?query:null;
    }
}
